USE master;

IF DB_ID('FIRMAKURIERSKA') IS NOT NULL DROP DATABASE FIRMAKURIERSKA;

IF @@ERROR = 3702 
   RAISERROR('Database cannot be dropped because there are still open connections.', 127, 127) WITH NOWAIT, LOG;

CREATE DATABASE FIRMAKURIERSKA;
GO

USE FIRMAKURIERSKA;
GO

CREATE TABLE Kierowca
(
  kid		INT	NOT NULL IDENTITY,
  imie		NVARCHAR(20) NOT NULL,
  nazwisko	NVARCHAR(20) NOT NULL,
  nr_tel	INT NOT NULL,
  
  CONSTRAINT PK_Kierowca PRIMARY KEY(kid),
);

CREATE TABLE Nadawca
(
  nid		INT	NOT NULL IDENTITY,
  imie		NVARCHAR(20) NOT NULL,
  nazwisko	NVARCHAR(20) NOT NULL,
  miasto	NVARCHAR(20) NOT NULL,
  ulica		NVARCHAR(20) NOT NULL,
  nr_domu	INT NOT NULL,
  nr_tel	INT NOT NULL,
  
  CONSTRAINT PK_Nadawca PRIMARY KEY(nid),
);

CREATE TABLE Adresat
(
  aid		INT	NOT NULL IDENTITY,
  imie		NVARCHAR(20) NOT NULL,
  nazwisko	NVARCHAR(20) NOT NULL,
  miasto	NVARCHAR(20) NOT NULL,
  ulica		NVARCHAR(20) NOT NULL,
  nr_domu	INT NOT NULL,
  nr_tel	INT NOT NULL,
  
  CONSTRAINT PK_Adresat PRIMARY KEY(aid),
);

CREATE TABLE Paczka
(
  pid					INT	NOT NULL IDENTITY,
  data_nadania			DATETIME NOT NULL,
  data_odebrania		DATETIME NOT NULL,
  waga					INT NOT NULL,
  w_bazie				BIT NOT NULL,
  dostarczona			BIT NOT NULL,
  kierowca_id			INT NOT NULL,
  adresat_id			INT NOT NULL,
  
  CONSTRAINT PK_Paczka PRIMARY KEY(pid),
  CONSTRAINT FK_Paczka_Kierowca FOREIGN KEY(kierowca_id)
	REFERENCES Kierowca(kid),
  CONSTRAINT FK_Paczka_Adresat FOREIGN KEY(adresat_id)
	REFERENCES Adresat(aid),
  CONSTRAINT CHK_data_nadania CHECK(data_nadania <= CURRENT_TIMESTAMP),
  CONSTRAINT CHK_data_odebrania CHECK(data_odebrania <= CURRENT_TIMESTAMP)
);

CREATE TABLE Samochod
(
  sid					INT	NOT NULL IDENTITY,
  ladownosc				INT NOT NULL,
  obciazenie			INT NOT NULL,
  w_bazie				BIT NOT NULL,
  nr_rejsetracyjny		INT NOT NULL,
  kierowca_id			INT NOT NULL,
  
  CONSTRAINT PK_Samochod PRIMARY KEY(sid),
  CONSTRAINT FK_Samochod_Kierowca FOREIGN KEY(kierowca_id)
	REFERENCES Kierowca(kid),
);

